from sqlalchemy.orm import Session
from sqlalchemy import and_,func
from typing import List
import datetime,operator
import models, schemas

'''
本文件负责定义处理数据库的函数
'''

def storage(db: Session, data:schemas.Upload):
    try:
        d = models.Pause(**data.dict())
        db.add(d)
        db.commit()
        db.refresh(models.Pause)
        return True
    except:
        return False

def analyse(db:Session, data:schemas.Upload):
    data_dict= {}
    line = data.line
    # 查找第一个恢复的数据
    end = db.query(models.Pause).filter(models.Pause.AlarmType.like("%恢复%"),models.Pause.line.like(line)).order_by(-models.Pause.DateTime).first()
    # 记录其数据
    if end:
        data_dict.update({'EndLine':end.line, 'EndVarName':end.VarName, 'EndAlarm':end.AlarmType, 'EndID': end.ID, 'EndTime':end.DateTime})
    else:
        print('急停数据库没有数据')
        return False
    # 查找下一个按下的数据
    start = db.query(models.Pause).filter(and_(
        models.Pause.line == end.line,
        models.Pause.VarName == end.VarName,
        models.Pause.AlarmType.like("%按下%"),
        models.Pause.DateTime <= end.DateTime
    )).first()
    if start:
        # 计算间隔时间
        interval = (end.DateTime - start.DateTime).seconds
        if interval>300:
            active = False
        else:
            active =True
        # 记录按下的数据
        data_dict.update({'StartLine': start.line, 'StartVarName': start.VarName, 'StartAlarm': start.AlarmType, 'StartID': start.ID,
                          'StartTime': start.DateTime, 'Interval':interval, 'isActive':active})
        new_data = models.Analyse_Pause(**data_dict)
        temp = db.query(models.Analyse_Pause).filter(models.Analyse_Pause.StartID == start.ID).first()
        if not temp:
            db.add(new_data)
            data_dict = {}
            db.commit()
        else:
            data_dict = {}



def get_data(db:Session, start:str, end:str ):

    result = {}
    # 获得在start和end时间段内的所有发生过的急停点时间
    from main import LineName
    for line in LineName:
        total_num = {}
        total_time = {}
        names = db.query(models.Analyse_Pause.EndVarName).distinct().filter(and_(
            models.Analyse_Pause.StartTime.between(start, end)),
            models.Analyse_Pause.EndLine.like(line)
        ).all()
        if names:
            for name in names:
                num = db.query(func.count(models.Analyse_Pause.StartAlarm)).filter(and_(
                    models.Analyse_Pause.EndVarName.like(name[0]),
                    models.Analyse_Pause.StartLine.like(line),
                    models.Analyse_Pause.StartTime.between(start, end),
                    models.Analyse_Pause.isActive == True
                )).first()
                time = db.query(func.sum(models.Analyse_Pause.Interval)).filter(and_(
                    models.Analyse_Pause.EndVarName.like(name[0]),
                    models.Analyse_Pause.StartLine.like(line),
                    models.Analyse_Pause.StartTime.between(start, end),
                    models.Analyse_Pause.isActive == True
                )).first()
                total_num.setdefault(name[0],num[0])
                total_time.setdefault(name[0],time[0])
            result.update({line:{'急停次数':total_num,'急停时间':total_time}})
        else:
            result.update({line: {'急停次数':'没有数据','急停时间':'没有数据'}})
    return result


def get_newdata(db:Session, start:str, end:str ):
    data = {}
    txt = {}
    from main import LineName
    for line in LineName:
        result = db.query(models.Analyse_Pause.EndVarName,func.count(models.Analyse_Pause.StartID),
                          func.sum(models.Analyse_Pause.Interval)).group_by(models.Analyse_Pause.EndVarName).filter(
            models.Analyse_Pause.EndLine == line,
            models.Analyse_Pause.StartTime.between(start, end),
            models.Analyse_Pause.isActive == True).order_by(-func.sum(models.Analyse_Pause.Interval)).all()
        if len(result) >= 3:
            text = '{0}-{1}，{2}线急停次数前三位是：\n{3}：\n按下{4}次，影响时间合计{5}秒\n{6}：\n' \
                           '按下{7}次，影响时间合计{8}秒\n{9}：\n按下{10}次，影响时间合计{11}秒'.format(
                start[10:-3],end[10:-3],line,result[0][0],result[0][1],result[0][2],
                result[1][0], result[1][1], result[1][2],
                result[2][0], result[2][1], result[2][2]
            )
            print(text)
            txt.update({line: text})
        else:
            text = '{0}线没有急停数据，请确认终端是否正确上传数据。。。'.format(line)
            print(text)
            txt.update({line:text})
        data.update({line:result})
    return (data,txt)